using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SharedSource.SqlServer.MDDE.Middleware;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Xml;

namespace Microsoft.SharedSource.SqlServer.MDDE.ManagementStudio
{
    public partial class InstanceCreator : Form
    {
        string repositoryConnectionString;
        string templateName;

        enum ServerAuthentication
        {
            WindowsAuthentication,
            SQLServerAuthentication
        }

        public InstanceCreator(string repositoryConnectionString,
            string templateName)
        {
            InitializeComponent();
            this.repositoryConnectionString =
                repositoryConnectionString;
            this.templateName = templateName;

            // Populate the authentication combobox.
            DataTable dtAuth = new DataTable();
            dtAuth.Columns.Add("Name", typeof(string));
            dtAuth.Columns.Add("Value", typeof(ServerAuthentication));
            dtAuth.Rows.Add("Windows Authentication",
                ServerAuthentication.WindowsAuthentication);
            dtAuth.Rows.Add("SQL Server Authentication",
                ServerAuthentication.SQLServerAuthentication);
            cmbAuthentication.DataSource = dtAuth;
            cmbAuthentication.ValueMember = "Value";
            cmbAuthentication.DisplayMember = "Name";
        }

        private void ReEnableControls()
        {
            foreach (Control ctrl in this.Controls)
                ctrl.Enabled = true;

            // Change mouse cursor.
            this.Cursor = Cursors.Default;

            // Disable the authentication part.
            cmbAuthentication.Enabled = false;
            txtPassword.Enabled = false;
            txtUserName.Enabled = false;
        }

        private void btnSelectAll_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow r in dgMain.Rows)
                r.Cells["select"].Value = true;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            // Disable controls.
            GrayoutControls();

            foreach (DataGridViewRow row in dgMain.Rows)
            {
                if (!Convert.ToBoolean(row.Cells["select"].Value))
                    continue;

                // Change the status.
                row.Cells["status"].Value = "In Progress";

                dgMain.Refresh();

                string instanceName =
                    row.Cells["instanceName"].Value.ToString();

                // Get the instance configuration data.
                DataSet dsInstance =
                    SqlHelper.ExecuteDataset(
                    PrepareConnectionString(),
                    CommandType.Text,
                    "SELECT ComponentName, ComponentType, ComponentUri, ColumnCollectionType, ColumnCollectionName, ColumnName, ScalarPropertyName, ScalarPropertyValue, SourceColumnName, SourceColumnUsage, DestinationColumnName FROM [" +
                    cmbTable.Text +
                    "] WHERE TemplateName = '" +
                    templateName +
                    "' AND InstanceName = '" +
                    instanceName +
                    "' ORDER BY ComponentUri, ComponentType, ColumnCollectionType, ColumnCollectionName, ColumnName, ScalarPropertyName, ScalarPropertyValue, SourceColumnName, SourceColumnUsage, DestinationColumnName;");

                // Create the configuration data document.
                XmlDocument xInstance = new XmlDocument();
                XmlElement xRootElement =
                    xInstance.CreateElement("TemplateInstance");
                xInstance.AppendChild(xRootElement);

                foreach (DataRow r0 in dsInstance.Tables[0].Rows)
                {
                    // Get the component information.
                    string componentName = r0["ComponentName"].ToString();
                    string componentUri = r0["ComponentUri"].ToString();
                    string componentType = r0["ComponentType"].ToString();
                    string columnCollectionType =
                        r0.IsNull("ColumnCollectionType") ? null :
                        r0["ColumnCollectionType"].ToString();
                    string columnCollectionName =
                        r0.IsNull("ColumnCollectionName") ? null :
                        r0["ColumnCollectionName"].ToString();
                    string columnName =
                        r0.IsNull("ColumnName") ? null :
                        r0["ColumnName"].ToString();
                    string scalarPropertyName =
                        r0.IsNull("ScalarPropertyName") ? null :
                        r0["ScalarPropertyName"].ToString();
                    string scalarPropertyValue =
                        r0.IsNull("ScalarPropertyValue") ? null :
                        r0["ScalarPropertyValue"].ToString();
                    string sourceColumnName =
                        r0.IsNull("SourceColumnName") ? null :
                        r0["SourceColumnName"].ToString();
                    bool sourceColumnUsage =
                        r0.IsNull("SourceColumnUsage") ? false :
                        Convert.ToBoolean(r0["SourceColumnUsage"]);
                    string destinationColumnName =
                        r0.IsNull("DestinationColumnName") ? null :
                        r0["DestinationColumnName"].ToString();

                    // Check for configurable columns.
                    if (columnName != null)
                        UIHelper.AppendColumnProperty(xRootElement,
                            componentName, componentType, componentUri,
                            columnCollectionType, columnCollectionName,
                            columnName, scalarPropertyName,
                            scalarPropertyValue);
                    else if (columnCollectionName != null)
                        UIHelper.AppendColumnCollectionProperty(xRootElement,
                            componentName, componentType, componentUri,
                            columnCollectionType, columnCollectionName,
                            scalarPropertyName, scalarPropertyValue);
                    else if (sourceColumnName != null)
                        UIHelper.AppendMapping(xRootElement,
                            componentName, componentType, componentUri,
                            sourceColumnName, sourceColumnUsage,
                            destinationColumnName);
                    else if (scalarPropertyName != null)
                        UIHelper.AppendComponentProperty(xRootElement,
                            componentName, componentType, componentUri,
                            scalarPropertyName, scalarPropertyValue);
                }

                try
                {
                    OperationResult result =
                        MDDEManager.AddTemplateInstance(
                        repositoryConnectionString,
                        instanceName, templateName, xInstance);

                    StringBuilder errorLog = new StringBuilder();
                    foreach (string logEntry in result.OperationLog)
                        errorLog.Append(logEntry);

                    if (result.SuccessStatus == false)
                        row.Cells["status"].Value = "Failure";
                    else
                        row.Cells["status"].Value = "Success";

                    // Update message.
                    row.Cells["message"].Value = errorLog.ToString();
                }
                catch (Exception ex)
                {
                    row.Cells["status"].Value = "Failure";

                    // Update message.
                    row.Cells["message"].Value =
                        "Error occured while creating the template instance." +
                        ex.Message;
                }
            }

            // Enable close button.
            btnClose.Enabled = true;

            // Change cursor.
            this.Cursor = Cursors.Default;
        }

        private void GrayoutControls()
        {
            // Disable controls.
            foreach (Control ctrl in this.Controls)
            {
                ctrl.Enabled = false;
                ctrl.Refresh();
            }


            // Change cursor.
            this.Cursor = Cursors.WaitCursor;
        }

        private void cmbServer_DropDown(object sender, EventArgs e)
        {
            // Populate the server name combobox.
            if (cmbServer.DataSource == null)
            {
                GrayoutControls();

                DataTable dt = SmoApplication.EnumAvailableSqlServers();
                cmbServer.DataSource = dt;
                cmbServer.DisplayMember = "Name";

                ReEnableControls();
            }
        }

        private void cmbServer_TextChanged(object sender, EventArgs e)
        {
            // Clear up the databases.
            cmbDatabase.DataSource = null;
        }

        private void cmbDatabase_DropDown(object sender, EventArgs e)
        {
            // Populate the database list if empty.
            if (cmbDatabase.DataSource == null)
            {
                try
                {
                    // Get the database list.
                    DataSet dtDatabases = SqlHelper.ExecuteDataset(
                        PrepareConnectionString(),
                        CommandType.Text,
                        "SELECT name FROM sys.databases");

                    if (dtDatabases.Tables[0].Rows.Count > 0)
                    {
                        cmbDatabase.DataSource = dtDatabases.Tables[0];
                        cmbDatabase.DisplayMember = "name";
                        cmbDatabase.ValueMember = "name";
                    }
                }
                catch
                {
                    // Gobble up all exceptions
                }
            }
        }

        private void cmbTable_DropDown(object sender, EventArgs e)
        {
            // Populate the tables list if empty.
            if (cmbTable.DataSource == null)
            {
                try
                {
                    // Get the tables list.
                    DataSet dsTables = SqlHelper.ExecuteDataset(
                        PrepareConnectionString(),
                        CommandType.Text,
                        "SELECT name FROM sys.tables");

                    if (dsTables.Tables[0].Rows.Count > 0)
                    {
                        cmbTable.DataSource = dsTables.Tables[0];
                        cmbTable.DisplayMember = "name";
                        cmbTable.ValueMember = "name";
                    }
                }
                catch
                {
                    // Gobble up all exceptions
                }
            }
        }

        private string PrepareConnectionString()
        {
            // Prepare a connection string.
            SqlConnectionStringBuilder sb =
                new SqlConnectionStringBuilder();

            sb.DataSource = cmbServer.Text;

            if (cmbAuthentication.SelectedValue is int
                && (ServerAuthentication)cmbAuthentication.SelectedValue ==
                ServerAuthentication.WindowsAuthentication)
                sb.IntegratedSecurity = true;
            else
            {
                sb.IntegratedSecurity = false;
                sb.UserID = txtUserName.Text;
                sb.Password = txtPassword.Text;
            }

            sb.InitialCatalog = cmbDatabase.Text;

            return sb.ConnectionString;
        }

        private bool ValidateTable()
        {
            if (cmbTable.Text == string.Empty)
            {
                return false;
            }

            // Validate the schema of selected table.
            try
            {
                Server server =
                    new Server(
                    new ServerConnection(
                    new SqlConnection(PrepareConnectionString())));
                Database database = server.Databases[cmbDatabase.Text];
                Table table = database.Tables[cmbTable.Text];

                if (
                    table.Columns.Contains("TemplateName") &&
                    table.Columns["TemplateName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("InstanceName") &&
                    table.Columns["InstanceName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ComponentName") &&
                    table.Columns["ComponentName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ComponentUri") &&
                    table.Columns["ComponentUri"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ColumnCollectionType") &&
                    table.Columns["ColumnCollectionType"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ColumnCollectionName") &&
                    table.Columns["ColumnCollectionName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ColumnName") &&
                    table.Columns["ColumnName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ScalarPropertyName") &&
                    table.Columns["ScalarPropertyName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("ScalarPropertyValue") &&
                    table.Columns["ScalarPropertyValue"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("SourceColumnName") &&
                    table.Columns["SourceColumnName"].
                    DataType.SqlDataType == SqlDataType.NVarChar &&
                    table.Columns.Contains("SourceColumnUsage") &&
                    table.Columns["SourceColumnUsage"].
                    DataType.SqlDataType == SqlDataType.Bit &&
                    table.Columns.Contains("DestinationColumnName") &&
                    table.Columns["DestinationColumnName"].
                    DataType.SqlDataType == SqlDataType.NVarChar
                    )
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        private void InstanceCreator_Load(object sender, EventArgs e)
        {
            ApplyEnablingLogic();
        }

        private void ApplyEnablingLogic()
        {
            // Enable/Disable Username, Password.
            if (cmbAuthentication.SelectedValue is int
                && (ServerAuthentication)cmbAuthentication.SelectedValue ==
                ServerAuthentication.SQLServerAuthentication)
            {
                txtUserName.Enabled = true;
                txtPassword.Enabled = true;
            }

            if (cmbAuthentication.SelectedValue is int
                && (ServerAuthentication)cmbAuthentication.SelectedValue ==
                ServerAuthentication.WindowsAuthentication)
            {
                txtUserName.Enabled = false;
                txtPassword.Enabled = false;
            }
        }

        private void cmbAuthentication_SelectedIndexChanged(
            object sender, EventArgs e)
        {
            ApplyEnablingLogic();
        }

        private void cmbServer_SelectedIndexChanged(object sender, EventArgs e)
        {
            cmbServer_TextChanged(sender, e);
        }

        private void cmbDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Clear up the tables.
            cmbTable.DataSource = null;
        }

        private void cmbTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Clear the datagrid.
            dgMain.Rows.Clear();

            // Validate the table.
            if (!ValidateTable())
                return;

            // Get the instances list.
            DataSet dsInstances =
                SqlHelper.ExecuteDataset(
                PrepareConnectionString(),
                CommandType.Text,
                "SELECT DISTINCT(InstanceName) FROM [" +
                cmbTable.Text +
                "] WHERE TemplateName = '" +
                templateName + "'");

            // Populate grid.
            foreach (DataRow r0 in dsInstances.Tables[0].Rows)
                dgMain.Rows.Add(false, r0["InstanceName"].ToString(),
                    string.Empty, string.Empty);
        }
    }
}